package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.dto.TransferSpecialCarCountDto;
import com.tianqu.tms.dto.TransferStatusCountDto;

public class TransferStatusCountDaoImp extends TmsDao implements
		TransferStatusCountDao {

	protected TransferStatusCountDaoImp() throws TmsDaoException {
		super();
		// TODO Auto-generated constructor stub
	}

	@Override
	public TransferStatusCountDto getTransferStatusCountInfo(String countDay)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" ID,   ");
		sql.append(" STATUS,  ");
		sql.append(" COUNT,  ");
		sql.append(" COUNTDAY ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_STATUS_COUNT);
		if(countDay!=null&&!"".equals(countDay))
		{
			sql.append(" WHERE DATE_FORMAT(COUNTDAY,'%Y-%m-%d')='"+countDay+"' ");
		}
		sql.append(" ORDER BY ID  ");

		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			TransferStatusCountDto dto = null;
			while (rs.next()) {
				dto = new TransferStatusCountDto();
				dto.setId(rs.getInt("ID"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setCount(rs.getInt("COUNT"));
				dto.setCountDay(DateUtil.getDateByTimeStamp(rs.getTimestamp("COUNTDAY")));
			}
			return dto; 
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferStatusCountDto> getTransferStatusCountList()
			throws TmsDaoException {
		List<TransferStatusCountDto> list = new ArrayList<TransferStatusCountDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" DISTINCT COUNTDAY AS COUNTDAY		");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_STATUS_COUNT);
		sql.append(" ORDER BY ID DESC ");

		try {
			ps = conn.prepareStatement(sql.toString());
			
			rs = ps.executeQuery();
			TransferStatusCountDto dto = null;
			while (rs.next()) {
				dto = new TransferStatusCountDto();
				dto.setCountDay(DateUtil.getDateByTimeStamp(rs.getTimestamp("COUNTDAY")));
				list.add(dto);
			}
			return list;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferStatusCountDto> getTransferStatusCountByWeekId(
			String countDay) throws TmsDaoException {
		List<TransferStatusCountDto> innerCarList = new ArrayList<TransferStatusCountDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" ID, ");
		sql.append(" STATUS, ");
		sql.append(" COUNT, ");
		sql.append(" COUNTDAY  ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_STATUS_COUNT);
		sql.append(" WHERE 1=1 ");
		sql.append(" AND DATE_FORMAT(COUNTDAY,'%Y-%m-%d') = '"+countDay+"' ");
		sql.append(" ORDER BY ID  ");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			TransferStatusCountDto dto = null;
			while (rs.next()) {
				dto = new TransferStatusCountDto();
				dto.setId(rs.getInt("ID"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setCount(rs.getInt("COUNT"));
				innerCarList.add(dto);
			}
			return innerCarList;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

}
